expdp/impdp 参数network |
您所在的位置：网站首页 › ora 39082 impdp 原因 › expdp/impdp 参数network |
|
下面是network_link常用的三个环境测试 1，服务器端的数据导出到指定的客户端 2，不同数据库间迁移数据。 3，同一个数据库中不同用户之间迁移数据。 一：服务器端的数据导出到指定的客户端 1，修改客户端的TNSNAMES文件 POWER1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.13)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = power1) ) ) 2，创建dblink SQL> CREATE PUBLIC DATABASE LINK "POWER1" 2 CONNECT TO scott 3 IDENTIFIED BY "oracle" 4 USING 'POWER1'; Database link created. SQL> select * from dual@power1; D - X 3，directory目录 SQL> set lines 170 SQL> col owner for a15 SQL> col directory_name for a60 SQL> col directory_name for a30 SQL> col DIRECTORY_PATH for a70 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- ------------------------------ ---------------------------------------------------------------------- SYS DUMP /tmp SYS TOAD_BDUMP_DIR /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace SYS XMLDIR /u01/app/oracle/product/11.2/db_1/rdbms/xml SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl11g/dpdump/ SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2/db_1/ccr/state 如果不存在使用create directory创建再用grant授予用户权限 4，导数据 [oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott Export: Release 11.2.0.3.0 - Production on Tue Mar 26 18:22:28 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31631: privileges are required ORA-39149: cannot link privileged user to non-privileged user grant这个命令要在源端数据库上面执行 SQL> GRANT exp_full_database TO scott; Grant succeeded. 回到客户端上面 [oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:40:03 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file "/tmp/scott_test.dmp" ORA-27038: created file already exists Additional information: 1 [oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott REUSE_DUMPFILES=Y Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:42:29 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link=power1 schemas=scott REUSE_DUMPFILES=Y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.129 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."TEST" 1.800 GB 19096576 rows . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/scott_test.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:46:1 已经成功导出到客户端指定的位置
二，不同数据库间迁移数据 环境接着上面的，这里把源端SCOTT用户的数据迁移到客户端上面的HTZ用户下面 [oracle11g@rhel4 admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 26 20:15:14 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop user htz cascade; User dropped. [oracle11g@rhel4 tmp]$ impdp system/oracle directory=dump logfile=scott_impdp.log remap_schema=scott:htz network_link=power1 Import: Release 11.2.0.3.0 - Production on Tue Mar 26 20:16:27 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=dump logfile=scott_impdp.log remap_schema=scott:htz network_link=power1 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.129 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "HTZ"."TEST" 19096576 rows . . imported "HTZ"."DEPT" 4 rows . . imported "HTZ"."EMP" 14 rows . . imported "HTZ"."SALGRADE" 5 rows . . imported "HTZ"."BONUS" 0 rows Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39082: Object type ALTER_PROCEDURE:"HTZ"."BBW_INSERT" created with compilation warnings Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 20:19:28三：同一个数据库中不同用户之间迁移数据 环境接着上面，这里把HTZ的数据库迁移到同一个数据库的SCOTT用户下面 1，删除SCOTT用户 [oracle11g@rhel4 admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 26 20:30:28 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop user scott cascade; User dropped. SQL> Drop public database link "orcl11g"; Database link dropped. SQL> SQL> create public database link "orcl11g" 2 connect to htz 3 identified by "oracle" 4 using 'ORCL11G'; Database link created. SQL> grant exp_full_database TO htz; Grant succeeded. 2，导数据 [oracle11g@rhel4 admin]$ impdp system/oracle directory=dump logfile=scott_impdp.log remap_schema=htz:scott network_link=orcl11g Import: Release 11.2.0.3.0 - Production on Tue Mar 26 20:34:25 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=dump logfile=scott_impdp.log remap_schema=htz:scott network_link=orcl11g Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.115 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "SCOTT"."TEST" 19096576 rows . . imported "SCOTT"."DEPT" 4 rows . . imported "SCOTT"."EMP" 14 rows . . imported "SCOTT"."SALGRADE" 5 rows . . imported "SCOTT"."BONUS" 0 rows Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."BBW_INSERT" created with compilation warnings Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 20:35:49三种常用的方式已经测试成功 本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/1163551，如需转载请自行联系原作者 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |